﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;

namespace ALDIOrganizer
{
    // This part of the class contains methods that insert records into the CurrentFile
    public static partial class SQLiteAccess
    {
        #region spawning new rows
        public static DataRow SpawnEmployeeRow()
        {
            return _dataset.Tables[0].NewRow();
        }

        public static DataRow SpawnContractRow()
        {
            return _dataset.Tables[1].NewRow();
        }

        public static DataRow SpawnHolidaysRow()
        {
            return _dataset.Tables[2].NewRow();
        }

        public static DataRow SpawnRequestRow()
        {
            return _dataset.Tables[3].NewRow();
        }
        #endregion



        public delegate void voidDelegate();
        public delegate void OnDataChangedDelegate(Int64 affectedID, int[] affectedTables, DateTime? affectedPeriodSTART, DateTime? affectedPeriodEND);

        public static event OnDataChangedDelegate OnDataChanged;
        public static event voidDelegate OnHolidayYearSpanChanged;
        

        /// <summary>
        /// retrieves the id (PK) of the last inserted row
        /// </summary>
        /// <returns>the primary key Value of the last row inserted to the CurrentFile</returns>
        /// <remarks>
        /// _howManyWeHave use it in order to maintain data consinstency between related tables.
        /// When a new _employee record is added, [...]
        /// </remarks>
        static Int64 GetIdentity()
        {
            var sc = new SQLiteCommand("SELECT last_insert_rowid()", _connection);
            object x = sc.ExecuteScalar();
            return (Int64)x;
        }

        /// <summary>
        /// Adds a new _employee to the CurrentFile
        /// </summary>
        /// <param Name="e">the row for the emps_from_database datatable</param>
        /// <param Name="pRefill">the row for the contracts datatable</param>
        /// <remarks>
        /// two tables are related.
        /// you cannot AddAnotherRowString an _employee without adding a _contract for him.
        /// (either somebody works for the company - or doesn't, and shouldn't be in the CurrentFile).
        /// _howManyWeHave use last_insert_rowid (GetIdentity) in order to check what [...]
        /// 
        /// but - obviously - it'pShift possible that something would happen after inserting the _employee,
        /// but before inserting the _contract for him.
        /// that'pShift why the operation is transactional. we either insert both rows - or None.
        /// thanks to this we avoid having our data corrupted.
        /// </remarks>
        internal static void AddNewEmployee(DataRow e, DataRow c)
        {
            SQLiteCommandBuilder builder = new SQLiteCommandBuilder(_adapter);
            SQLiteTransaction transaction;
            transaction = _connection.BeginTransaction();

            #region emps_from_database
            try
            {
                var insert = new SQLiteCommand(_connection);
                
                insert.CommandText = @"INSERT INTO employees VALUES ( null, "
                                    + @"@firstName, @lastName, "
                                    + @"@phone, @mobile, "
                                    + @"@birthDate, @sex, "
                                    + @"@address, @nin, "
                                    + @"'', '', " // current_Status, current_employedNow [both to be calculated]
                                    + @"@current_offProd, @current_contractHrs, " // current_offProd (extension methd!!
                                    + @"@current_holidayAllowance, @current_Position, "
                                    + @"'qweqwe', @onvisa) "; // first one is displayColumn

                insert.Parameters.AddWithValue("firstName", e[1]);
                insert.Parameters.AddWithValue("lastName", e[2]);
                insert.Parameters.AddWithValue("phone", e[3]);
                insert.Parameters.AddWithValue("mobile", e[4]);

                insert.Parameters.AddWithValue("birthDate", e[5]);
                insert.Parameters.AddWithValue("sex", (bool)e[6] ? 1 : 0);
                insert.Parameters.AddWithValue("address", e[7]);
                insert.Parameters.AddWithValue("nin", e[8]);
                // [9] = current_Status [to be calculated]
                // [10] = current_employedNow [to be calculated]                
                insert.Parameters.AddWithValue("current_offProd", e[11]); // extension method!!! [to be calculated from _role]
                insert.Parameters.AddWithValue("current_contractHrs", c[5]);
                insert.Parameters.AddWithValue("current_holidayAllowance", c[6]);
                insert.Parameters.AddWithValue("current_Position", c[4]);
                insert.Parameters.AddWithValue("onvisa", (bool)e[16] ? 1 : 0);

                DataTable employees = _dataset.Tables[0];

                employees.Rows.Add(e);
                insert.Transaction = transaction;

                _adapter.InsertCommand = insert;
                _adapter.SelectCommand = new SQLiteCommand("SELECT * FROM employees; SELECT * FROM contracts", _connection);

                _adapter.Update(_dataset.Tables[0].GetChanges(DataRowState.Added));
                _dataset.Tables[0].AcceptChanges();
                
                

            #endregion



                var identity = MarkLastRowWithLastInsertID(_dataset.Tables[0], 0);

                DataTable employmentHistory = _dataset.Tables[1];

                c[1] = identity;

                insert = new SQLiteCommand(_connection);
                insert.CommandText = @"INSERT INTO contracts VALUES ( null, '" + identity.ToString() + "', " // EmployeeID, EmployeeID [foreign key]
                                    + @"@date, 1, "
                                    + @"@position, @contractHRS, "
                                    + @"@holidayAllowance, @comments, '')";

                insert.Parameters.AddWithValue("date", c[2]);
                insert.Parameters.AddWithValue("position", c[4]);
                insert.Parameters.AddWithValue("contractHrs", c[5]);
                insert.Parameters.AddWithValue("holidayAllowance", c[6]);
                insert.Parameters.AddWithValue("comments", c[7]);
                insert.Transaction = transaction;


                employmentHistory.Rows.Add(c);                

                _adapter.InsertCommand = insert;
                
                _adapter.Update(_dataset.Tables[1].GetChanges(DataRowState.Added)); // cały?
                var foo = MarkLastRowWithLastInsertID(_dataset.Tables[1], 0);
                _dataset.Tables[1].AcceptChanges();

                transaction.Commit();

                CalculateColumns(DateTime.Now, new int[] { 0, 1 }, identity);
                OnDataChanged(identity, new int[] { 0, 1 }, (DateTime?)c[2], (DateTime?)null);
                
                
                
            }
            catch (Exception x)
            {
                transaction.Rollback();
                Program.LogError("transaction failed", x);
            }
            

            
        }

        private static Int64 MarkLastRowWithLastInsertID(DataTable table, int column)
        {
            var identity = GetIdentity();
            table.Rows[table.Rows.Count - 1][column] = identity;
            return identity;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param Name="EmployeeID"></param>
        /// <param Name="pRefill"></param>        
        /// <remarks>
        /// 1. It is caller'pShift responsibility to make sure the date (or pRefill[2]) is not a duplicate.
        /// (An _employee shouldn't have two different contracts On the same Day, since this would be
        /// ambiguous.)
        /// This method updates
        /// </remarks>
        internal static void AddNewContract(Int64 empID, DataRow c)
        {
            // checking for duplicates (contracts with the same date)
            var duplicates = from contract in _dataset.Tables[1].AsEnumerable()
                             where contract.Field<DateTime>("date") == (DateTime)c[2]
                             select contract;
            if(duplicates.Any())            
            {
                // TODO: update existing one
                return;
            }
            SQLiteCommandBuilder builder = new SQLiteCommandBuilder(_adapter);

            /* I repeat myself a bit here,
             * but in AddNewEmployee method the _contract is being added as part of transaction */
            var insert = new SQLiteCommand(_connection);
            insert.CommandText  = @"INSERT INTO contracts VALUES ( null, '" + empID.ToString() + "', "
                                + @"@date, @hired, "
                                + @"@position, @contractHRS, "
                                + @"@holidayAllowance, @comments, '')";

            insert.Parameters.AddWithValue("date", c[2]);
            insert.Parameters.AddWithValue("hired", c[3]);
            insert.Parameters.AddWithValue("position", c[4]);
            insert.Parameters.AddWithValue("contractHrs", c[5]);
            insert.Parameters.AddWithValue("holidayAllowance", c[6]);
            insert.Parameters.AddWithValue("comments", c[7]);

            c[1] = empID;
            _dataset.Tables[1].Rows.Add(c);
            _adapter.InsertCommand = insert;
            _adapter.Update(_dataset.Tables[1].GetChanges(DataRowState.Added));
            var foo = MarkLastRowWithLastInsertID(_dataset.Tables[1], 0);
            _dataset.Tables[1].AcceptChanges();
            
            OnDataChanged(empID,new int[]{0,1},(DateTime?)c[2],(DateTime?)null);
        }

        internal static void AddNewHoliday(Int64 empID, DataRow h)
        {
            SQLiteCommandBuilder builder = new SQLiteCommandBuilder(_adapter);
                        
            var insert = new SQLiteCommand(_connection);
            insert.CommandText = @"INSERT INTO holidays VALUES ( null, '" + empID.ToString() + "', "
                                + @"@startDate, @endDate, "
                                + @"@type, @pending, "
                                + @"@comments, '')";

            insert.Parameters.AddWithValue("startDate", h[2]);
            insert.Parameters.AddWithValue("endDate", h[3]);
            insert.Parameters.AddWithValue("type", h[4]);
            insert.Parameters.AddWithValue("pending", h[5]);            
            insert.Parameters.AddWithValue("comments", h[6]);

            var _minmax_before = GetHolidaysYearsMinMax();
            var call = ((DateTime)h[2]).Year <_minmax_before.Key | ((DateTime)h[3]).Year > _minmax_before.Value;

            h[1] = empID;
            _dataset.Tables[2].Rows.Add(h);
            _adapter.InsertCommand = insert;
            _adapter.Update(_dataset.Tables[2].GetChanges(DataRowState.Added));
            var foo = MarkLastRowWithLastInsertID(_dataset.Tables[2], 0);
            _dataset.Tables[2].AcceptChanges();

            // calling events
            OnDataChanged(empID,new int[] { 2},(DateTime?)h[2],(DateTime?)h[3]);
            if (call) OnHolidayYearSpanChanged();                      
        }        

        internal static void AddNewRequest(Int64 empID, DataRow r)
        {
            var _builder = new SQLiteCommandBuilder(_adapter);
            var _insert = new SQLiteCommand(_connection);

            _insert.CommandText = @"INSERT INTO requests VALUES ( null, '"
                + empID.ToString() + "', "
                + @"@startDate, @endDate, "
                + @"@startHour, @endHour, "
                + @"@weekdays, @pending, @comments)";

            _insert.Parameters.AddWithValue("startDate", r[2]);
            _insert.Parameters.AddWithValue("endDate", r[3]);
            _insert.Parameters.AddWithValue("startHour", r[4]);
            _insert.Parameters.AddWithValue("endHour", r[5]);
            _insert.Parameters.AddWithValue("weekdays", r[6]);
            _insert.Parameters.AddWithValue("pending", r[7]);
            _insert.Parameters.AddWithValue("comments", r[8]);

            r[1] = empID;
            _dataset.Tables[3].Rows.Add(r);
            _adapter.InsertCommand = _insert;
            _adapter.Update(_dataset.Tables[3].GetChanges(DataRowState.Added));
            var foo = MarkLastRowWithLastInsertID(_dataset.Tables[3], 0);
            _dataset.Tables[3].AcceptChanges();

            OnDataChanged(empID,
                new int[] { 3 },
                new Request(r).startDate,
                new Request(r).endDate);
        }

        

    }
}
